﻿using FAMailUtils.Data.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAMailUtils.Data.DAO
{
    public class AccountDAO
    {
        DbHelper db = new DbHelper();
        public AccountDTO GetByAccountID(int AccountID, int ClientID)
        {
            string sql = "select * from tblMB_Accounts where AccountID = " + AccountID + " and ClientID = " + ClientID;
            AccountDTO result = new AccountDTO();
            result = (AccountDTO)db.GetObject(result, sql);
            return result;
        }

        public DataTable Search(string email, string accountName)
        {
            string sql = "select * from tblMB_Accounts where Email like '%" + email + "%' and AccountName like '%" + accountName + "%'";
            DataTable T = db.GetTable(sql);
            return T;
        }

        public bool SetMainDepartment(AccountDTO dto)
        {
            if (dto.IsMainDepartment == true)
            {
                string sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where AccountID = @AccountID";
                object obj = db.ExecuteScalar(sql, new object[] { "AccountID", dto.AccountID, "IsMainDepartment", 0 });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
            else
            {
                string sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where DepartmentID = @DepartmentID";
                object obj = db.ExecuteScalar(sql, new object[] { "DepartmentID", dto.DepartmentID, "IsMainDepartment", 0 });

                sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where AccountID = @AccountID";
                obj = db.ExecuteScalar(sql, new object[] { "AccountID", dto.AccountID, "IsMainDepartment", 1 });

                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
        }

        public bool Block(int AccountID, int ClientID)
        {
            string sql = "select * from tblMB_Accounts where AccountID = " + AccountID + " and ClientID = " + ClientID;
            AccountDTO dto = new AccountDTO();
            dto = (AccountDTO)db.GetObject(dto, sql);

            if (dto.IsBlock == false)
            {
                string sql1 = "update tblMB_Accounts set IsBlock = 1, BlockDate = getdate() from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
                object obj = db.ExecuteScalar(sql1, new object[] { "AccountID", AccountID, "ClientID", ClientID });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
            else
            {
                string sql1 = "update tblMB_Accounts set IsBlock = 0 from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
                object obj = db.ExecuteScalar(sql1, new object[] { "AccountID", AccountID, "ClientID", ClientID });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
        }

        public bool Delete(int AccountID, int ClientID)
        {
            string sql = "update tblMB_Accounts set isDelete = 1, deletedate = getdate() from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "AccountID", AccountID, "ClientID", ClientID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public bool ResetPassword(int AccountID, int ClientID, string Password)
        {
            string sql = "update tblMB_Accounts set Password = @Password from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "AccountID", AccountID, "ClientID", ClientID, "Password", Password });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public DataTable GetAll(int clientID)
        {
            string sql = @"select a.* from tblMB_Accounts a where a.IsDelete = 0 and a.ClientID = @ClientID";
            DataTable T = db.GetTable(sql, new object[] { "ClientID", clientID });
            return T;
        }

        public int Create(AccountDTO dto)
        {
            string sql = @"insert into tblMB_Accounts 
                (AccountName, CreateDate, ClientID, IsBlock, IsMainDepartment, IsDelete, Phone, Password, [Function], Email, Address, DepartmentID, UserID) 
                values (@AccountName, @CreateDate, @ClientID, @IsBlock, @IsMainDepartment, @IsDelete, @Phone, @Password, @Function, @Email, @Address, @DepartmentID,@userID) select @@identity";
            object obj = db.ExecuteScalar(sql, new object[] { 
                "AccountName", dto.AccountName, 
                "CreateDate", dto.CreateDate,
                "ClientID", dto.ClientID, 
                "IsBlock", dto.IsBlock,
                "IsMainDepartment", dto.IsMainDepartment,
                "IsDelete", false,
                "Phone", dto.Phone,
                "Password", dto.Password,
                "Function", dto.Function, 
                "Email", dto.Email,
                "Address", dto.Address,
                "DepartmentID", dto.DepartmentID,
                "UserID", dto.UserID
            });
            int result = 0;
            int.TryParse(obj + "", out result);
            return result;
        }

        public bool Update(AccountDTO dto)
        {
            string sql = @"update tblMB_Accounts set AccountName = @AccountName, 
                        Phone = @Phone, 
                        Password = @Password, 
                        [Function] = @Function,
                        Email = @Email,
                        DepartmentID = @DepartmentID,
                        Address = @Address from tblMB_Accounts where AccountID = @AccountID";
                object obj = db.ExecuteScalar(sql, new object[] { "AccountName", dto.AccountName, 
                    "Phone", dto.Phone, 
                    "Password", dto.Password, 
                    "Function", dto.Function, 
                    "Email", dto.Email,
                    "Address", dto.Address,
                    "DepartmentID", dto.DepartmentID,
                    "AccountID", dto.AccountID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }
        public UserLoginDTO GetUserLogin(string userName, string pass)
        {

            pass = Common.GetMd5Hash(pass.Trim());
            UserLoginDTO result = null;
            //string sql = "select * From tblClient where userid = (select userid from tbluserlogin where username=@username and password=@password)";
            string sql = "select * from tbluserlogin a left join tblClient b on b.userid = a.userid where username=@username and password=@password and deleted=0";
            DataTable table = db.GetTable(sql, new object[] { "username", userName, "password", pass });
            if (table != null && table.Rows.Count > 0)
            {
                result = new UserLoginDTO();
                result.UserType = (UserType)table.Rows[0]["UserType"];
                result.UserStatus = Convert.ToInt32(table.Rows[0]["UserStatus"]);
                result.UserName = table.Rows[0]["UserName"] + "";
                if (table.Rows[0]["Clientid"] + "" != "")
                {
                    ClientDTO client = new ClientDTO();
                    client.ClientID = Convert.ToInt32(table.Rows[0]["Clientid"]);
                    client.UserID = Convert.ToInt32(table.Rows[0]["Userid"]);
                    client.Email = userName;
                    client.ClientName = table.Rows[0]["clientname"] + "";
                    client.Password = table.Rows[0]["Password"] + "";
                    client.CompanyLogo = table.Rows[0]["CompanyLogo"] + "";
                    client.Address = table.Rows[0]["Address"] + "";
                    client.Phone = table.Rows[0]["Phone"] + "";
                    client.cardId = table.Rows[0]["cardId"] + "";
                    if (table.Rows[0]["ExpireDate"] + "" != "")
                        client.ExpireDate = Convert.ToDateTime(table.Rows[0]["ExpireDate"] + "");
                    result.Client = client;
                }

            }
            else
            {
                sql = "select * From tblMB_Accounts where isDelete=0 and email = @UserName and Password = @Password";
                AccountDTO user = (AccountDTO)db.GetObject(new AccountDTO(), sql, new object[] { "UserName", userName, "Password", pass });
                if (user != null && user.AccountID != 0)
                {
                    result = new UserLoginDTO();
                    result.UserType = UserType.SubAccount;
                    result.Account = user;

                    sql = "select * from tbluserlogin a inner join tblClient b on b.userid = a.userid where a.userid=@userid";
                    table = db.GetTable(sql, new object[] { "userid", user.UserID });
                    if (table != null && table.Rows.Count > 0)
                    {
                        ClientDTO client = new ClientDTO();
                        client.ClientID = Convert.ToInt32(table.Rows[0]["Clientid"]);
                        client.UserID = Convert.ToInt32(table.Rows[0]["Userid"]);
                        client.Email = userName;
                        client.ClientName = table.Rows[0]["clientname"] + "";
                        client.Password = table.Rows[0]["Password"] + "";
                        client.CompanyLogo = table.Rows[0]["CompanyLogo"] + "";
                        client.Address = table.Rows[0]["Address"] + "";
                        client.Phone = table.Rows[0]["Phone"] + "";
                        client.cardId = table.Rows[0]["cardId"] + "";
                        if (table.Rows[0]["ExpireDate"] + "" != "")
                            client.ExpireDate = Convert.ToDateTime(table.Rows[0]["ExpireDate"] + "");
                        result.Client = client;
                    }
                }
            }

            return result;
        }
        public bool Customer_Active(int Id)
        {
            string sql = "UPDATE tblClient SET " +
                        "status = " + (int)Common.clientStatus.Normal + " where clientid=" + Id;
            sql += " and status=" + (int)Common.clientStatus.InActive;

            int i = db.ExecuteNoneQuery(sql);
            if (i > 0)
            {
                sql = "Update tblUserLogin set is_block=0, userstatus=" + (int)Common.clientStatus.Normal + " where userid=(select userid from tblclient where clientid=" + Id + ")";
                i = db.ExecuteNoneQuery(sql);
            }
            return i > 0;
        }
        public DataTable LoadThongTinDangKy(int id)
        {
            string sql = "SP_Client_LoadThongTinDangKy " + id;
            DataTable table = db.GetTable(sql);
            return table;
        }

        public DataTable LoadDepartmentFunctionByParentID(int parentid, int Departmentid, int userid)
        {
            string sql = "SP_MB_GetDepartmentFunctionByParentID";
            DataTable table = db.GetTable(sql, new object[] { "parentid", parentid, "Departmentid", Departmentid, "userid", userid });
            return table;
        }
        public DataTable LoadAccountFunctionByParentID(int parentid, int accountid, int userid)
        {
            string sql = "SP_MB_GetAccountFunctionByParentID";
            DataTable table = db.GetTable(sql, new object[] { "parentid", parentid, "accountid", accountid, "userid", userid });
            return table;
        }


        public void SaveFunctionByDepartmentID(string ids, int Departmentid, int userid)
        {
            string sql = "delete from tblMB_DepartmentFunctions where Departmentid=@Departmentid and Departmentid in (select Departmentid from tblMB_Departments where userid=@userid and isdelete=0)";
            db.ExecuteNoneQuery(sql, new object[] { "Departmentid", Departmentid, "userid", userid });
            foreach (string temp in ids.Split(','))
            {
                int id = 0;
                if (int.TryParse(temp, out id) && id > 0)
                {
                    sql = "insert into tblMB_DepartmentFunctions(DepartmentID, FunctionID)values(@Departmentid, @functionid)";
                    db.ExecuteNoneQuery(sql, new object[] { "Departmentid", Departmentid, "functionid", id });
                }
            }
        }

        public void SaveFunctionByAccountID(string ids, int accountid, int userid)
        {
            string sql = "delete from tblMB_AccountFunctions where accountid=@accountid and accountid in (select accountid from tblMB_Accounts where userid=@userid and isdelete=0)";
            db.ExecuteNoneQuery(sql, new object[] { "accountid", accountid, "userid", userid });
            foreach (string temp in ids.Split(','))
            {
                int id = 0;
                if (int.TryParse(temp, out id) && id > 0)
                {
                    sql = "insert into tblMB_AccountFunctions(accountid, FunctionID)values(@accountid, @functionid)";
                    db.ExecuteNoneQuery(sql, new object[] { "accountid", accountid, "functionid", id });
                }
            }
        }

        public bool CheckRole(UserLoginDTO userLogin, string url)
        {
            if (userLogin.UserType == UserType.CustomerAccount)
                return true;
            else
            {
                string sql = "SP_Account_GetUserPermission";
                DataTable data = db.GetTable(sql, new object[]{"AccountID",userLogin.Account.AccountID, "URL", url});
                return data.Rows.Count>0;
            }
        }

        public DataTable GetPermissionByAccountID(int accountid)
        {
            string sql = "[SP_Account_GetUserPermissionByAccountID]";
            return db.GetTable(sql, new object[] { "AccountID", accountid});
        }

        public DataTable GetAccountLimitInfo(int clientid)
        {
            string sql = "[SP_Account_GetAccountLimitInfo]";
            return db.GetTable(sql, new object[] { "clientid", clientid });

        }
    }
}
